/**
 * 报表导出路由
 */

const express = require('express');
const ExcelJS = require('exceljs');
const router = express.Router();
const { Pool } = require('pg');

// 数据库连接池
const pool = new Pool({
  user: process.env.DB_USER || 'postgres',
  host: process.env.DB_HOST || '127.0.0.1',
  database: process.env.DB_NAME || 'legacyDb',
  password: process.env.DB_PASSWORD || 'postgres',
  port: parseInt(process.env.DB_PORT || '5432'),
});

// 字段映射配置
const fieldMapping = {
  record_date: '日期',
  line_name: '生产线',
  order_number: '订单号',
  material_code: '物料编码',
  product_name: '产品名称',
  quantity: '完成数量',
  working_people: '线上人数',
  offline_staff: '线外人数',
  total_people: '总人数',
  calculated_pph: '实际PPH',
  target_pph: '目标PPH',
  efficiency_rate: '达成率',
  operator: '操作员',
  remarks: '备注'
};

// 导出Excel报表
router.get('/records/export', async (req, res) => {
  try {
    console.log('📊 开始导出报表...');
    
    // 解析查询参数
    const {
      start_date,
      end_date,
      line_name,
      order_number,
      material_code,
      product_name,
      efficiency_operator,
      efficiency_value,
      fields = 'record_date,line_name,order_number,material_code,product_name,quantity,working_people,offline_staff,total_people,calculated_pph,target_pph,efficiency_rate'
    } = req.query;

    // 构建SQL查询
    let query = `
      SELECT 
        record_date,
        line_name,
        order_number,
        material_code,
        product_name,
        quantity,
        working_people,
        offline_staff,
        total_people,
        calculated_pph,
        target_pph,
        efficiency_rate,
        operator,
        remarks
      FROM pph_records 
      WHERE 1=1
    `;
    
    const params = [];
    let paramIndex = 1;

    // 添加查询条件
    if (start_date) {
      query += ` AND record_date >= $${paramIndex}`;
      params.push(start_date);
      paramIndex++;
    }
    
    if (end_date) {
      query += ` AND record_date <= $${paramIndex}`;
      params.push(end_date);
      paramIndex++;
    }
    
    if (line_name) {
      query += ` AND line_name ILIKE $${paramIndex}`;
      params.push(`%${line_name}%`);
      paramIndex++;
    }
    
    if (order_number) {
      query += ` AND order_number ILIKE $${paramIndex}`;
      params.push(`%${order_number}%`);
      paramIndex++;
    }
    
    if (material_code) {
      query += ` AND material_code ILIKE $${paramIndex}`;
      params.push(`%${material_code}%`);
      paramIndex++;
    }
    
    if (product_name) {
      query += ` AND product_name ILIKE $${paramIndex}`;
      params.push(`%${product_name}%`);
      paramIndex++;
    }
    
    // 达成率条件
    if (efficiency_operator && efficiency_value) {
      const operators = {
        'gt': '>',
        'gte': '>=',
        'eq': '=',
        'lte': '<=',
        'lt': '<'
      };
      
      if (operators[efficiency_operator]) {
        query += ` AND efficiency_rate ${operators[efficiency_operator]} $${paramIndex}`;
        params.push(parseFloat(efficiency_value));
        paramIndex++;
      }
    }
    
    query += ' ORDER BY record_date DESC, line_name ASC';
    
    console.log('🔍 执行查询:', query);
    console.log('📝 查询参数:', params);
    
    // 执行查询
    const result = await pool.query(query, params);
    const records = result.rows;
    
    console.log(`📋 查询到 ${records.length} 条记录`);
    
    // 解析要导出的字段
    const exportFields = fields.split(',').filter(field => field.trim());
    
    // 创建Excel工作簿
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('PPH报表');
    
    // 设置列标题
    const headers = exportFields.map(field => fieldMapping[field] || field);
    worksheet.addRow(headers);
    
    // 设置标题行样式
    const headerRow = worksheet.getRow(1);
    headerRow.font = { bold: true, color: { argb: 'FFFFFF' } };
    headerRow.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: '4472C4' }
    };
    headerRow.alignment = { horizontal: 'center', vertical: 'middle' };
    
    // 添加数据行
    records.forEach(record => {
      const rowData = exportFields.map(field => {
        let value = record[field];
        
        // 格式化特殊字段
        if (field === 'record_date' && value) {
          value = new Date(value).toLocaleDateString('zh-CN');
        } else if (field === 'efficiency_rate' && value !== null) {
          value = `${parseFloat(value).toFixed(1)}%`;
        } else if (['calculated_pph', 'target_pph'].includes(field) && value !== null) {
          value = parseFloat(value).toFixed(1);
        }
        
        return value || '';
      });
      
      worksheet.addRow(rowData);
    });
    
    // 自动调整列宽
    worksheet.columns.forEach((column, index) => {
      const header = headers[index];
      let maxLength = header.length;
      
      column.eachCell({ includeEmpty: false }, (cell) => {
        const cellLength = cell.value ? cell.value.toString().length : 0;
        if (cellLength > maxLength) {
          maxLength = cellLength;
        }
      });
      
      column.width = Math.min(Math.max(maxLength + 2, 10), 30);
    });
    
    // 设置响应头
    const filename = `PPH报表_${new Date().toISOString().slice(0, 10)}.xlsx`;
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename="${encodeURIComponent(filename)}"`);
    
    // 输出Excel文件
    await workbook.xlsx.write(res);
    res.end();
    
    console.log('✅ 报表导出完成');
    
  } catch (error) {
    console.error('❌ 导出报表失败:', error);
    res.status(500).json({
      success: false,
      message: '导出失败',
      error: error.message
    });
  }
});

module.exports = router;
